package com.navi.loaddata.loadService.impl.opeHis;

import com.navi.loaddata.core.errorLog.ErrorCode;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.gpEntity.MOpeD;
import com.navi.loaddata.core.gpEntity.MProdD;
import com.navi.loaddata.core.gpEntity.hisToGP.ProductHis;
import com.navi.loaddata.core.gpEntity.hisToGP.WppShtOpeHisExt;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import com.navi.loaddata.core.util.ProcessUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Created by ghost on 2018/6/12.
 */
@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager")
public class LoadOpeHisInfoService {
    private static final Logger logger = LoggerFactory.getLogger(LoadOpeHisInfoService.class);
    private LoggerUtil loggerUtil = new LoggerUtil(this.getClass());
    @Autowired
    private GreenPlumRepositoryImpl gpDao;


    public List<ProductHis> getTrackOutInfo(EtlConfDModel etlConfD) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String startTime = sdf.format(etlConfD.getRunStartTimestamp().getTime());
        String endTime = sdf.format(etlConfD.getRunEndTimestamp().getTime());

        List<ProductHis> productHisList = new ArrayList<>();

        QueryRunner qr_mes = JdbcUtil.getQueryRunner_mes();
        try {
            String sql = "SELECT" +
                    " TrackOut.SUBFACTORYNAME AS shop_id," +
                    " TrackOut.ARRAYGLASSNAME AS TFT_GLASS_ID," +
                    " TrackOut.CFGLASSNAME AS CF_GLASS_ID," +
                    " TrackOut.PRODUCTNAME AS GLASS_ID_O," +
                    " TrackOut.VIRTUALGLASSNAME as virtualglassname," +
                    " TrackOut.LINKKEY AS LINKKEY," +
                    " TrackOut.PRODUCTSPECNAME AS PROD_ID," +
                    " TrackOut.EVENTUSER AS EVT_USER," +
                    " TrackOut.INSPTARGETEQP AS UNIT_EQ," +
                    " TrackOut.PRODUCTGRADE AS grade," +
                    " TrackOut.OLDPROCESSOPERATIONNAME AS OPE_ID," +
                    " TrackOut.MACHINENAME AS EQPT_ID," +
                    " TrackOut.MACHINERECIPENAME AS RECIPE_ID," +
                    " CASE WHEN TrackOut.OLDPROCESSOPERATIONNAME='ARRAY' OR TrackOut.GROUPNAME IS NULL THEN TrackOut.LOTNAME ELSE TrackOut.GROUPNAME END  AS lot_id," +
                    " TrackOut.POSITION AS POSITION," +
                    " TrackOut.PRODUCTSPECVERSION AS PRODUCT_SPECVERSION," +
                    " TrackOut.CARRIERNAME AS CARRIER_NAME," +
                    " TrackOut.REWORKCOUNT AS REWORK_COUNT," +
                    " TrackOut.SYSTEMTIME AS MES_TIMESTAMP," +
                    " TrackOut.EVENTTIME AS LOGOF_TIMESTAMP," +
                    " TrackOut.MASKNAME AS MASK_ID," +
                    " NULL AS ABNORMAL_FLAG_1," +//没做
                    " TrackOut.PRODUCTNAME AS PANEL_ID," +
                    " NULL AS SN," +//没做
                    " NULL AS BARE_GLASS_ID," +//没做
                    " NULL AS BARE_PRODUCT," +//没做
                    " CASE WHEN TrackOut.SUBFACTORYNAME='OC' THEN REQUEST.ERPPRODUCTREQUESTNAME ELSE NULL END AS WORKORDER_ID_RTP," +
                    " SPEC.PRODUCTTHICKNESS AS THICKNESS," +
                    " CASE WHEN TrackOut.SUBFACTORYNAME='OC' AND TrackOut.OLDPROCESSOPERATIONNAME IN('L4600','L4610','L4670') THEN TrackOut.PRODUCTGRADE ELSE NULL END OC_PNL_GRADE_CT1," +
                    " CASE WHEN TrackOut.SUBFACTORYNAME='OC' AND TrackOut.OLDPROCESSOPERATIONNAME ='L8100' THEN TrackOut.PRODUCTGRADE ELSE NULL END OC_PNL_GRADE_C_INSP," +
                    " NULL AS GRADE_WNS_SHIP," +
                    " CASE WHEN TrackOut.SUBFACTORYNAME='OC' AND TrackOut.OLDPROCESSOPERATIONNAME IN('L4600','L4610','L4670') THEN TrackOut.DEFECTCODES ELSE NULL END OC_PNL_LOSSCODE_CT1," +
                    " CASE WHEN TrackOut.SUBFACTORYNAME='OC' AND TrackOut.OLDPROCESSOPERATIONNAME ='L8100' THEN TrackOut.DEFECTCODES ELSE NULL END OC_PNL_lOSSCODE_C_INSP," +
                    " NULL AS POL_LOT_ID_TFT," +//没做
                    " NULL AS POL_PART_NO_TFT," +//没做
                    " NULL AS POL_LOT_ID_CF," +//没做
                    " NULL AS POL_PART_NO_CF," +//没做
                    " NULL AS PPBOX_ID," +//没做
                    " NULL AS PALLET_ID," +//没做
                    " NULL AS DEST_CUSTOMER_ID," +//没做
                    " TrackOut.OWNERID AS OWNER," +
                    " TrackOut.OWNERTYPE AS OWNERTYPE," +
                    " TrackOut.PRODUCTIONTYPE AS PRODUCTIONTYPE" +
                    " FROM" +
                    " P1RPTADM.MES_PRODUCTHISTORY TrackOut" +
                    " LEFT JOIN MES_PRODUCTSPEC SPEC ON TrackOut.PRODUCTSPECNAME=SPEC.PRODUCTSPECNAME AND TrackOut.PRODUCTSPECVERSION=SPEC.PRODUCTSPECVERSION AND TrackOut.SUBFACTORYNAME=SPEC.FACTORYNAME" +
                    " LEFT JOIN MES_PRODUCTREQUEST REQUEST ON TrackOut.PRODUCTREQUESTNAME=REQUEST.PRODUCTREQUESTNAME" +
                    " WHERE" +
                    " TrackOut.EVENTNAME='TrackOut'" +
                    " and TrackOut.UPDATETIME>=to_date('" + startTime + "','yyyymmddhh24miss')" +
                    " and TrackOut.UPDATETIME<to_date('" + endTime + "','yyyymmddhh24miss')" +
                    shopForSql(etlConfD.getShop()) +
                    opeForSql(etlConfD.getOpe()) +
                    eqptForSql(etlConfD.getEqpt());
            productHisList = qr_mes.query(sql, new BeanListHandler<ProductHis>(ProductHis.class));
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_MES_HIS_TRACTOUT, e);
        }
        return productHisList;
    }


    public WppShtOpeHisExt getExtInfo(String panelId) {
        try {
            long startTime = System.currentTimeMillis();
            List<WppShtOpeHisExt> wppExtList = gpDao.list(WppShtOpeHisExt.class, "from WppShtOpeHisExt where panelId='" + panelId + "'");
            long endTime = System.currentTimeMillis();
            long time = endTime - startTime;
            logger.warn("该片{} 查询ext的时间：{}", panelId, time);

            startTime = System.currentTimeMillis();
            if (!wppExtList.isEmpty()) {//存在
                endTime = System.currentTimeMillis();
                time = endTime - startTime;
                logger.warn("该片{} 不查询ext2的时间：{}", panelId, time);
                return wppExtList.get(0);
            } else {
                WppShtOpeHisExt wppExt = new WppShtOpeHisExt();
                wppExtList = gpDao.list(WppShtOpeHisExt.class, "from WppShtOpeHisExt where panelId='" + panelId.substring(0, 10) + "'");


                if (!wppExtList.isEmpty()) {
                    wppExt.setPanelId(panelId);
                    wppExt.setSn(ProcessUtil.getStringS(wppExtList.get(0).getSn()));
                    wppExt.setBareGlassId(ProcessUtil.getStringS(wppExtList.get(0).getBareGlassId()));
                    wppExt.setBareProduct(ProcessUtil.getStringS(wppExtList.get(0).getBareProduct()));
                    wppExt.setProductIdErp(ProcessUtil.getStringS(wppExtList.get(0).getProductIdErp()));
                    wppExt.setWorkorderId(ProcessUtil.getStringS(wppExtList.get(0).getWorkorderId()));
                    wppExt.setWorkorderIdRtp(ProcessUtil.getStringS(wppExtList.get(0).getWorkorderIdRtp()));
                    wppExt.setThickness(ProcessUtil.getBigDecimal(wppExtList.get(0).getThickness()));
                    wppExt.setGlassJudgeAry(ProcessUtil.getStringS(wppExtList.get(0).getGlassJudgeAry()));
                    wppExt.setGlassGradeAry(ProcessUtil.getStringS(wppExtList.get(0).getGlassGradeAry()));
                    wppExt.setTftPnlGrade(ProcessUtil.getStringS(wppExtList.get(0).getTftPnlGrade()));
                    wppExt.setGlassJudgeCf(ProcessUtil.getStringS(wppExtList.get(0).getGlassJudgeCf()));
                    wppExt.setGlassGradeCf(ProcessUtil.getStringS(wppExtList.get(0).getGlassGradeCf()));
                    wppExt.setCfPnlGrade(ProcessUtil.getStringS(wppExtList.get(0).getCfPnlGrade()));
                    wppExt.setOcPnlGradeCt1(ProcessUtil.getStringS(wppExtList.get(0).getOcPnlGradeCt1()));
                    wppExt.setOcPnlGradeCInsp(ProcessUtil.getStringS(wppExtList.get(0).getOcPnlGradeCInsp()));
                    wppExt.setGradeWnsShip(ProcessUtil.getStringS(wppExtList.get(0).getGradeWnsShip()));
                    wppExt.setOcPnlLosscodeCt1(ProcessUtil.getStringS(wppExtList.get(0).getOcPnlLosscodeCt1()));
                    wppExt.setOcPnlLosscodeCInsp(ProcessUtil.getStringS(wppExtList.get(0).getOcPnlLosscodeCInsp()));
                    wppExt.setPolLotIdTft(ProcessUtil.getStringS(wppExtList.get(0).getPolLotIdTft()));
                    wppExt.setPolPartNoTft(ProcessUtil.getStringS(wppExtList.get(0).getPolPartNoTft()));
                    wppExt.setPolLotIdCf(ProcessUtil.getStringS(wppExtList.get(0).getPolLotIdCf()));
                    wppExt.setPolPartNoCf(ProcessUtil.getStringS(wppExtList.get(0).getPolPartNoCf()));
                    wppExt.setPpboxId(ProcessUtil.getStringS(wppExtList.get(0).getPpboxId()));
                    wppExt.setPalletId(ProcessUtil.getStringS(wppExtList.get(0).getPalletId()));
                    wppExt.setDestCustomerId(ProcessUtil.getStringS(wppExtList.get(0).getDestCustomerId()));

                } else {
                    wppExt.setPanelId(panelId);
                }

                return wppExt;

            }
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_GP_OPE_HIS_EXT, e);
        }
    }


    private String shopForSql(String shop) {
        if (StringUtils.isEmpty(shop)) return "";
        return " AND TrackOut.SUBFACTORYNAME like '" + StringUtils.replace(shop, "*", "%") + "'";
    }

    private String opeForSql(String ope) {
        if (StringUtils.isEmpty(ope)) return "";
        return " AND TrackOut.OLDPROCESSOPERATIONNAME like'" + StringUtils.replace(ope, "*", "%") + "'";
    }

    private String eqptForSql(String eqpt) {
        if (StringUtils.isEmpty(eqpt)) return "";
        return " AND TrackOut.MACHINENAME like'" + StringUtils.replace(eqpt, "*", "%") + "'";
    }


    public void clearObject(WppShtOpeHisExt wppExt) {
        Session session = gpDao.getEntityManagerSession();
        session.evict(wppExt);//防止HIBERNATE自动保存实体
    }


    public HashMap<String, Integer> getProdMap() throws Exception {
        List<MProdD> mProdDS = new ArrayList<>();
        HashMap<String, Integer> prodMap = new HashMap<>();
        try {
            mProdDS = gpDao.list(MProdD.class, "from MProdD");
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_GP_OPEHIS_PRODUCT, e);
        }
        mProdDS.forEach(prod -> {
            int prodKey = prod.getProdKey();
            String prodId = prod.getProdId();
            String shopId = prod.getShopId();
            prodMap.put(prodId + "@" + shopId, prodKey);
        });
        return prodMap;
    }

    public HashMap<String, Integer> getOpeMap() throws Exception {
        List<MOpeD> mOpeDS = new ArrayList<>();
        HashMap<String, Integer> opeMap = new HashMap<>();
        try {
            mOpeDS = gpDao.list(MOpeD.class, "from MOpeD");
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_GP_OPEHIS_OPE, e);
        }
        mOpeDS.forEach(ope -> {
            int opeKey = ope.getOpeKey();
            String opeId = ope.getOpeId();
            String shopId = ope.getShopId();
            opeMap.put(opeId + "@" + shopId, opeKey);
        });
        return opeMap;
    }

    public List<ProductHis> getAshtTrackIn(String shop_id, String ope_id, String glass_id_o, String linkkey, String virtualglassname) {
        List<ProductHis> productHisList = new ArrayList<>();
        QueryRunner qr_mes = JdbcUtil.getQueryRunner_mes();
        String sql = "SELECT" +
                " MAX(LOGON_TIMESTAMP) AS LOGON_TIMESTAMP,MAX(GLASS_JUDGE_ARY) AS GLASS_JUDGE_ARY," +
                " MAX(GLASS_GRADE_ARY) AS GLASS_GRADE_ARY,MAX(TFT_PNL_GRADE) AS TFT_PNL_GRADE," +
                " MAX(tft_start_timestamp) AS tft_start_timestamp,MAX(tft_shipping_timestamp) AS tft_shipping_timestamp" +
                " FROM (SELECT" +
                " CASE WHEN LINKKEY = '" + linkkey + "' THEN LOGON_TIMESTAMP ELSE NULL END LOGON_TIMESTAMP," +
                " CASE WHEN FLG2=0 THEN NG_MARK ELSE NULL END AS NG_MARK," +
                " CASE WHEN FLG2=0 THEN GLASS_JUDGE_ARY ELSE NULL END GLASS_JUDGE_ARY," +
                " CASE WHEN FLG2=0 THEN GLASS_GRADE_ARY ELSE NULL END GLASS_GRADE_ARY," +
                " CASE WHEN FLG2=0 THEN TFT_PNL_GRADE ELSE NULL END TFT_PNL_GRADE," +
                " CASE WHEN FLG1=0 THEN tft_start_timestamp ELSE NULL END tft_start_timestamp," +
                " CASE WHEN FLG2=0 THEN tft_shipping_timestamp ELSE NULL END tft_shipping_timestamp" +
                " FROM (SELECT" +
                " LINKKEY," +
                " CASE WHEN EVENTNAME='TrackIn' THEN EVENTTIME ELSE NULL END AS LOGON_TIMESTAMP," +
                " CASE WHEN EVENTNAME='Ship' AND BANKLOCATION='A9960' THEN NGMARK ELSE NULL END AS NG_MARK," +
                " CASE WHEN EVENTNAME='Ship' THEN PRODUCTJUDGE ELSE NULL END GLASS_JUDGE_ARY," +
                " CASE WHEN EVENTNAME='Ship' THEN PRODUCTGRADE ELSE NULL END GLASS_GRADE_ARY," +
                " CASE WHEN EVENTNAME='Ship' THEN PANELGRADES ELSE NULL END TFT_PNL_GRADE," +
                " CASE WHEN  EVENTNAME='TrackIn' AND OLDPROCESSOPERATIONNAME='A1200' THEN EVENTTIME ELSE NULL END tft_start_timestamp," +
                " CASE WHEN  EVENTNAME='Ship' AND BANKLOCATION='A9960' THEN EVENTTIME ELSE NULL END tft_shipping_timestamp," +
                " LAG(1,1,0) OVER(partition BY PRODUCTNAME,OLDPROCESSOPERATIONNAME,EVENTNAME ORDER BY EVENTTIME ASC) AS FLG1," +
                " LAG(1,1,0) OVER(partition BY PRODUCTNAME,OLDPROCESSOPERATIONNAME,EVENTNAME ORDER BY EVENTTIME DESC) AS FLG2" +
                " FROM" +
                " P1RPTADM.MES_PRODUCTHISTORY " +
                " WHERE" +
                " SUBFACTORYNAME = '" + shop_id + "'" +
                " AND PRODUCTNAME ='" + getA1100GlassId(glass_id_o, virtualglassname) + "'" +
                " AND OLDPROCESSOPERATIONNAME = '" + ope_id + "'" +
                " AND EVENTNAME IN( 'TrackIn','Ship')))";
        try {
            productHisList = qr_mes.query(sql, new BeanListHandler<ProductHis>(ProductHis.class));
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_GP_OPE_HIS_ASHT, e);
        }
        return productHisList;
    }

    public List<ProductHis> getFshtTrackIn(String shop_id, String ope_id, String glass_id_o, String linkkey) {
        List<ProductHis> productHisList = new ArrayList<>();
        QueryRunner qr_mes = JdbcUtil.getQueryRunner_mes();
        String sql = "SELECT" +
                " MAX(LOGON_TIMESTAMP) AS LOGON_TIMESTAMP,MAX(NG_MARK) AS NG_MARK,MAX(GLASS_JUDGE_CF) AS GLASS_JUDGE_CF," +
                " MAX(GLASS_GRADE_CF) AS GLASS_GRADE_CF,MAX(CF_PNL_GRADE) AS CF_PNL_GRADE," +
                "MAX(cf_start_timestamp) AS cf_start_timestamp,MAX(cf_shipping_timestamp) AS cf_shipping_timestamp" +
                " FROM (SELECT" +
                " CASE WHEN LINKKEY = '" + linkkey + "' THEN LOGON_TIMESTAMP ELSE NULL END LOGON_TIMESTAMP," +
                " CASE WHEN FLG2=0 THEN NG_MARK ELSE NULL END AS NG_MARK," +
                " CASE WHEN FLG2=0 THEN GLASS_JUDGE_CF ELSE NULL END GLASS_JUDGE_CF," +
                " CASE WHEN FLG2=0 THEN GLASS_GRADE_CF ELSE NULL END GLASS_GRADE_CF," +
                " CASE WHEN FLG2=0 THEN CF_PNL_GRADE ELSE NULL END CF_PNL_GRADE," +
                " CASE WHEN FLG1=0 THEN cf_start_timestamp ELSE NULL END cf_start_timestamp," +
                " CASE WHEN FLG2=0 THEN cf_shipping_timestamp ELSE NULL END cf_shipping_timestamp" +
                " FROM (SELECT" +
                " LINKKEY," +
                " CASE WHEN EVENTNAME='TrackIn' THEN EVENTTIME ELSE NULL END AS LOGON_TIMESTAMP," +
                " CASE WHEN EVENTNAME='Ship' AND BANKLOCATION='C9960' THEN NGMARK ELSE NULL END AS NG_MARK," +
                " CASE WHEN SUBFACTORYNAME='CF' AND EVENTNAME='Ship' THEN PRODUCTJUDGE ELSE NULL END GLASS_JUDGE_CF," +
                " CASE WHEN SUBFACTORYNAME='CF' AND EVENTNAME='Ship' THEN PRODUCTGRADE ELSE NULL END GLASS_GRADE_CF," +
                " CASE WHEN SUBFACTORYNAME='CF' AND EVENTNAME='Ship' THEN PANELGRADES ELSE NULL END CF_PNL_GRADE," +
                " CASE WHEN  EVENTNAME='TrackIn' AND OLDPROCESSOPERATIONNAME='C1300' THEN EVENTTIME ELSE NULL END cf_start_timestamp," +
                " CASE WHEN  EVENTNAME='Ship' AND BANKLOCATION='C9960' THEN EVENTTIME ELSE NULL END cf_shipping_timestamp," +
                " LAG(1,1,0) OVER(partition BY PRODUCTNAME,OLDPROCESSOPERATIONNAME,EVENTNAME ORDER BY EVENTTIME ASC) AS FLG1," +
                " LAG(1,1,0) OVER(partition BY PRODUCTNAME,OLDPROCESSOPERATIONNAME,EVENTNAME ORDER BY EVENTTIME DESC) AS FLG2" +
                " FROM" +
                " P1RPTADM.MES_PRODUCTHISTORY" +
                " WHERE" +
                " SUBFACTORYNAME = '" + shop_id + "'" +
                " AND PRODUCTNAME ='" + glass_id_o + "'" +
                " AND OLDPROCESSOPERATIONNAME = '" + ope_id + "'" +
//                " AND MACHINENAME = '" + eqpt_id + "'" +
//                " AND REWORKCOUNT = '" + rework_count + "'" +
//                " AND LINKKEY = '" + linkkey + "'" +
                " AND EVENTNAME in ('TrackIn','Ship')))";
        try {
            productHisList = qr_mes.query(sql, new BeanListHandler<ProductHis>(ProductHis.class));
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_GP_OPE_HIS_FSHT, e);
        }
        return productHisList;
    }


    public List<ProductHis> getCshtTrackIn(String shop_id, String ope_id, String glass_id_o, String linkkey) {
        List<ProductHis> productHisList = new ArrayList<>();
        QueryRunner qr_mes = JdbcUtil.getQueryRunner_mes();
        String sql = "SELECT" +
                " MAX(LOGON_TIMESTAMP) AS LOGON_TIMESTAMP,MAX(NG_MARK) AS NG_MARK," +
                " MAX(PRODUCT_ID_ERP) AS PRODUCT_ID_ERP," +
                " MAX(WORKORDER_ID) AS WORKORDER_ID," +
                " MAX(oc_start_timestamp) AS oc_start_timestamp,MAX(oc1_bank_in_timestamp) AS oc1_bank_in_timestamp," +
                " MAX(oc2_start_timestamp) AS oc2_start_timestamp,MAX(oc_shipping_timestamp) AS oc_shipping_timestamp" +
                " FROM (SELECT" +
                " CASE WHEN LINKKEY = '" + linkkey + "' THEN LOGON_TIMESTAMP ELSE NULL END AS LOGON_TIMESTAMP," +
                " CASE WHEN FLG2=0 THEN NG_MARK ELSE NULL END AS NG_MARK," +
                " CASE WHEN FLG1=0 THEN PRODUCT_ID_ERP ELSE NULL END AS PRODUCT_ID_ERP," +
                " CASE WHEN FLG1=0 THEN WORKORDER_ID ELSE NULL END AS WORKORDER_ID," +
                " CASE WHEN FLG1=0 THEN oc_start_timestamp ELSE NULL END oc_start_timestamp," +
                " CASE WHEN FLG2=0 THEN oc1_bank_in_timestamp ELSE NULL END oc1_bank_in_timestamp," +
                " CASE WHEN FLG1=0 THEN oc2_start_timestamp ELSE NULL END oc2_start_timestamp," +
                " CASE WHEN FLG2=0 THEN oc_shipping_timestamp ELSE NULL END oc_shipping_timestamp" +
                " FROM (SELECT" +
                " HIS.LINKKEY AS LINKKEY," +
                " CASE WHEN HIS.EVENTNAME='TrackIn' THEN HIS.EVENTTIME ELSE NULL END AS LOGON_TIMESTAMP," +
                " CASE WHEN HIS.EVENTNAME='Ship' AND HIS.BANKLOCATION='L9100' THEN NGMARK ELSE NULL END AS NG_MARK," +
                " CASE WHEN HIS.EVENTNAME='TrackIn' AND HIS.OLDPROCESSOPERATIONNAME='L1100' THEN SPEC.ERPPRODUCTSPECNAME ELSE NULL END AS PRODUCT_ID_ERP," +
                " CASE WHEN HIS.EVENTNAME='TrackIn' AND HIS.OLDPROCESSOPERATIONNAME='L1100' THEN HIS.PRODUCTREQUESTNAME ELSE NULL END AS WORKORDER_ID," +
                " CASE WHEN  HIS.EVENTNAME='TrackIn' AND HIS.OLDPROCESSOPERATIONNAME='L1100' THEN HIS.EVENTTIME ELSE NULL END oc_start_timestamp," +
                " CASE WHEN  HIS.EVENTNAME='BankIn' AND HIS.BANKLOCATION='L5000' THEN HIS.EVENTTIME ELSE NULL END oc1_bank_in_timestamp," +
                " CASE WHEN  HIS.EVENTNAME='TrackIn' AND HIS.OLDPROCESSOPERATIONNAME='L5100' THEN HIS.EVENTTIME ELSE NULL END oc2_start_timestamp," +
                " CASE WHEN  HIS.EVENTNAME='Ship' AND HIS.BANKLOCATION='L9100' THEN HIS.EVENTTIME ELSE NULL END oc_shipping_timestamp," +
                " LAG(1,1,0) OVER(partition BY HIS.PRODUCTNAME,HIS.OLDPROCESSOPERATIONNAME,HIS.EVENTNAME ORDER BY HIS.EVENTTIME ASC) AS FLG1," +
                " LAG(1,1,0) OVER(partition BY HIS.PRODUCTNAME,HIS.OLDPROCESSOPERATIONNAME,HIS.EVENTNAME ORDER BY HIS.EVENTTIME DESC) AS FLG2" +
                " FROM" +
                " P1RPTADM.MES_PRODUCTHISTORY HIS" +
                " LEFT JOIN MES_PRODUCTSPEC SPEC ON HIS.PRODUCTSPECNAME=SPEC.PRODUCTSPECNAME AND HIS.PRODUCTSPECVERSION=SPEC.PRODUCTSPECVERSION AND HIS.SUBFACTORYNAME=SPEC.FACTORYNAME" +
                " WHERE" +
                " HIS.SUBFACTORYNAME = '" + shop_id + "'" +
                " AND HIS.PRODUCTNAME ='" + caseWhen4100(ope_id, glass_id_o) + "'" +
                " AND HIS.OLDPROCESSOPERATIONNAME = '" + ope_id + "'" +
//                " AND MACHINENAME = '" + eqpt_id + "'" +
//                " AND REWORKCOUNT = '" + rework_count + "'" +
//                " AND LINKKEY = '" + linkkey + "'" +
                " AND HIS.EVENTNAME in ('TrackIn','Ship','BankIn')))";
        try {
            productHisList = qr_mes.query(sql, new BeanListHandler<ProductHis>(ProductHis.class));
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_GP_OPE_HIS_CSHT, e);
        }
        return productHisList;
    }


    public List<MProdD> getMProdDS(String prod_id, String shop_id) {
        List<MProdD> mProdDS = new ArrayList<>();
        try {
            mProdDS = gpDao.list(MProdD.class, "from MProdD where prodId = ? and shopId = ?", prod_id, shop_id);
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_GP_OPEHIS_PRODUCT, e);
        }
        return mProdDS;
    }

    public List<MOpeD> getMOpeDS(String ope_id, String shop_id) {
        List<MOpeD> mOpeDS = new ArrayList<>();
        try {
            mOpeDS = gpDao.list(MOpeD.class, "from MOpeD where opeId = ? and shopId = ?", ope_id, shop_id);
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_GP_OPEHIS_OPE, e);
        }
        return mOpeDS;
    }

    private String caseWhen4100(String ope_id, String glass_id_o) {
        if ("L4100".equals(ope_id)) return glass_id_o.substring(0, 10);
        return glass_id_o;
    }

    public HashMap<String, ProductHis> getShopStartTimestamp(List<ProductHis> productHisList) {
        HashMap<String, ProductHis> shopStartTime = new HashMap<>();

        QueryRunner qr_mes = JdbcUtil.getQueryRunner_mes();
        StringBuffer glassIdOpes = new StringBuffer();
        productHisList.forEach(glass -> glassIdOpes.append("('" + glass.getGlass_id_o() + "','" + glass.getOpe_id() + "'),"));
        glassIdOpes.deleteCharAt(glassIdOpes.length() - 1);

        String sql = "SELECT " +
                " PRODUCTNAME AS glass_id," +
                " OLDPROCESSOPERATIONNAME AS ope_id," +
                " CASE OLDPROCESSOPERATIONNAME WHEN 'A1200' THEN MIN(EVENTTIME) END tft_start_timestamp," +
                " CASE OLDPROCESSOPERATIONNAME WHEN 'C1300' THEN MIN(EVENTTIME) END cf_start_timestamp," +
                " CASE OLDPROCESSOPERATIONNAME WHEN 'L5100' THEN MIN(EVENTTIME) END oc2_start_timestamp" +
                " FROM MES_PRODUCTHISTORY" +
                " WHERE EVENTNAME='TrackIn'" +
                " AND OLDPROCESSOPERATIONNAME in('A1200','C1300','L5100')" +
                " and (PRODUCTNAME,OLDPROCESSOPERATIONNAME) IN (" + glassIdOpes + ")" +
                " GROUP BY PRODUCTNAME,OLDPROCESSOPERATIONNAME";
        try {
            productHisList = qr_mes.query(sql, new BeanListHandler<ProductHis>(ProductHis.class));
            productHisList.forEach(glass -> shopStartTime.put(glass.getGlass_id() + "_" + glass.getOpe_id(), glass));
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_MES_HIS_SHOP_STARTTIMESTAMP, e);
        }
        return shopStartTime;
    }

    public ProductHis getA1100TrackOut(String virtualglassname, String glass_id) {
        List<ProductHis> productHisList = new ArrayList<>();
        try {
            QueryRunner qr_mes = JdbcUtil.getQueryRunner_mes();
            String sql = "SELECT" +
                    " TrackOut.SUBFACTORYNAME AS shop_id," +
                    " TrackOut.ARRAYGLASSNAME AS TFT_GLASS_ID," +
                    " TrackOut.CFGLASSNAME AS CF_GLASS_ID," +
                    " '" + glass_id + "' AS GLASS_ID_O," +
                    " '" + virtualglassname + "' as virtualglassname," +
                    " TrackOut.LINKKEY AS LINKKEY," +
                    " TrackOut.PRODUCTSPECNAME AS PROD_ID," +
                    " TrackOut.EVENTUSER AS EVT_USER," +
                    " TrackOut.INSPTARGETEQP AS UNIT_EQ," +
                    " TrackOut.PRODUCTGRADE AS grade," +
                    " TrackOut.OLDPROCESSOPERATIONNAME AS OPE_ID," +
                    " TrackOut.MACHINENAME AS EQPT_ID," +
                    " TrackOut.MACHINERECIPENAME AS RECIPE_ID," +
                    " CASE WHEN TrackOut.OLDPROCESSOPERATIONNAME='ARRAY' OR TrackOut.GROUPNAME IS NULL THEN TrackOut.LOTNAME ELSE TrackOut.GROUPNAME END  AS lot_id," +
                    " TrackOut.POSITION AS POSITION," +
                    " TrackOut.PRODUCTSPECVERSION AS PRODUCT_SPECVERSION," +
                    " TrackOut.CARRIERNAME AS CARRIER_NAME," +
                    " TrackOut.REWORKCOUNT AS REWORK_COUNT," +
                    " TrackOut.SYSTEMTIME AS MES_TIMESTAMP," +
                    " TrackOut.EVENTTIME AS LOGOF_TIMESTAMP," +
                    " TrackOut.MASKNAME AS MASK_ID," +
                    " NULL AS ABNORMAL_FLAG_1," +//没做
                    " TrackOut.PRODUCTNAME AS PANEL_ID," +
                    " NULL AS SN," +//没做
                    " NULL AS BARE_GLASS_ID," +//没做
                    " NULL AS BARE_PRODUCT," +//没做
                    " CASE WHEN TrackOut.SUBFACTORYNAME='OC' THEN REQUEST.ERPPRODUCTREQUESTNAME ELSE NULL END AS WORKORDER_ID_RTP," +
                    " SPEC.PRODUCTTHICKNESS AS THICKNESS," +
                    " CASE WHEN TrackOut.SUBFACTORYNAME='OC' AND TrackOut.OLDPROCESSOPERATIONNAME IN('L4600','L4610','L4670') THEN TrackOut.PRODUCTGRADE ELSE NULL END OC_PNL_GRADE_CT1," +
                    " CASE WHEN TrackOut.SUBFACTORYNAME='OC' AND TrackOut.OLDPROCESSOPERATIONNAME ='L8100' THEN TrackOut.PRODUCTGRADE ELSE NULL END OC_PNL_GRADE_C_INSP," +
                    " NULL AS GRADE_WNS_SHIP," +
                    " CASE WHEN TrackOut.SUBFACTORYNAME='OC' AND TrackOut.OLDPROCESSOPERATIONNAME IN('L4600','L4610','L4670') THEN TrackOut.DEFECTCODES ELSE NULL END OC_PNL_LOSSCODE_CT1," +
                    " CASE WHEN TrackOut.SUBFACTORYNAME='OC' AND TrackOut.OLDPROCESSOPERATIONNAME ='L8100' THEN TrackOut.DEFECTCODES ELSE NULL END OC_PNL_lOSSCODE_C_INSP," +
                    " NULL AS POL_LOT_ID_TFT," +//没做
                    " NULL AS POL_PART_NO_TFT," +//没做
                    " NULL AS POL_LOT_ID_CF," +//没做
                    " NULL AS POL_PART_NO_CF," +//没做
                    " NULL AS PPBOX_ID," +//没做
                    " NULL AS PALLET_ID," +//没做
                    " NULL AS DEST_CUSTOMER_ID," +//没做
                    " TrackOut.OWNERID AS OWNER," +
                    " TrackOut.OWNERTYPE AS OWNERTYPE," +
                    " TrackOut.PRODUCTIONTYPE AS PRODUCTIONTYPE" +
                    " FROM" +
                    " P1RPTADM.MES_PRODUCTHISTORY TrackOut" +
                    " LEFT JOIN MES_PRODUCTSPEC SPEC ON TrackOut.PRODUCTSPECNAME=SPEC.PRODUCTSPECNAME AND TrackOut.PRODUCTSPECVERSION=SPEC.PRODUCTSPECVERSION AND TrackOut.SUBFACTORYNAME=SPEC.FACTORYNAME" +
                    " LEFT JOIN MES_PRODUCTREQUEST REQUEST ON TrackOut.PRODUCTREQUESTNAME=REQUEST.PRODUCTREQUESTNAME" +
                    " WHERE" +
                    " TrackOut.EVENTNAME='TrackOut'" +
                    " AND TrackOut.OLDPROCESSOPERATIONNAME='A1100'" +
                    " AND TrackOut.PRODUCTNAME='" + virtualglassname + "'";
            productHisList = qr_mes.query(sql, new BeanListHandler<ProductHis>(ProductHis.class));
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_MES_HIS_TRACTOUT, e);
        }
        if (productHisList.isEmpty()) return null;
        return productHisList.get(0);
    }

    public String getA1100GlassId(String glass_id_o, String virtualglassname) {
        if (StringUtils.isEmpty(virtualglassname)) return glass_id_o;
        return virtualglassname;
    }

    public HashMap<String,WppShtOpeHisExt> getExtInfoList(HashMap<String, String> glassIds) {
        HashMap<String, WppShtOpeHisExt> extList = new HashMap<>();
        StringBuffer hql = new StringBuffer();
        hql.append("from WppShtOpeHisExt where 1=1 and(");
        for (String e : glassIds.keySet()) {
            hql.append(" panelId='" + e + "' or");
        }
        hql.deleteCharAt(hql.length() - 1).deleteCharAt(hql.length() - 1).append(")");
        try {
            List<WppShtOpeHisExt> wppExtList = gpDao.list(WppShtOpeHisExt.class, hql.toString());
            wppExtList.forEach(e -> extList.put(e.getPanelId(), e));
            if (!wppExtList.isEmpty() && glassIds.size() == extList.size()) return extList;


            //panel找glassId
            for (String panelId : glassIds.keySet()) {
                if (null == extList.get(panelId)) {
                    WppShtOpeHisExt wppExt = new WppShtOpeHisExt();
                    wppExt.setPanelId(panelId);
                    wppExtList = gpDao.list(WppShtOpeHisExt.class, "from WppShtOpeHisExt where panelId='" + panelId.substring(0, 10) + "'");
                    if (!wppExtList.isEmpty()) {
                        wppExt.setSn(ProcessUtil.getStringS(wppExtList.get(0).getSn()));
                        wppExt.setBareGlassId(ProcessUtil.getStringS(wppExtList.get(0).getBareGlassId()));
                        wppExt.setBareProduct(ProcessUtil.getStringS(wppExtList.get(0).getBareProduct()));
                        wppExt.setProductIdErp(ProcessUtil.getStringS(wppExtList.get(0).getProductIdErp()));
                        wppExt.setWorkorderId(ProcessUtil.getStringS(wppExtList.get(0).getWorkorderId()));
                        wppExt.setWorkorderIdRtp(ProcessUtil.getStringS(wppExtList.get(0).getWorkorderIdRtp()));
                        wppExt.setThickness(ProcessUtil.getBigDecimal(wppExtList.get(0).getThickness()));
                        wppExt.setGlassJudgeAry(ProcessUtil.getStringS(wppExtList.get(0).getGlassJudgeAry()));
                        wppExt.setGlassGradeAry(ProcessUtil.getStringS(wppExtList.get(0).getGlassGradeAry()));
                        wppExt.setTftPnlGrade(ProcessUtil.getStringS(wppExtList.get(0).getTftPnlGrade()));
                        wppExt.setGlassJudgeCf(ProcessUtil.getStringS(wppExtList.get(0).getGlassJudgeCf()));
                        wppExt.setGlassGradeCf(ProcessUtil.getStringS(wppExtList.get(0).getGlassGradeCf()));
                        wppExt.setCfPnlGrade(ProcessUtil.getStringS(wppExtList.get(0).getCfPnlGrade()));
                        wppExt.setOcPnlGradeCt1(ProcessUtil.getStringS(wppExtList.get(0).getOcPnlGradeCt1()));
                        wppExt.setOcPnlGradeCInsp(ProcessUtil.getStringS(wppExtList.get(0).getOcPnlGradeCInsp()));
                        wppExt.setGradeWnsShip(ProcessUtil.getStringS(wppExtList.get(0).getGradeWnsShip()));
                        wppExt.setOcPnlLosscodeCt1(ProcessUtil.getStringS(wppExtList.get(0).getOcPnlLosscodeCt1()));
                        wppExt.setOcPnlLosscodeCInsp(ProcessUtil.getStringS(wppExtList.get(0).getOcPnlLosscodeCInsp()));
                        wppExt.setPolLotIdTft(ProcessUtil.getStringS(wppExtList.get(0).getPolLotIdTft()));
                        wppExt.setPolPartNoTft(ProcessUtil.getStringS(wppExtList.get(0).getPolPartNoTft()));
                        wppExt.setPolLotIdCf(ProcessUtil.getStringS(wppExtList.get(0).getPolLotIdCf()));
                        wppExt.setPolPartNoCf(ProcessUtil.getStringS(wppExtList.get(0).getPolPartNoCf()));
                        wppExt.setPpboxId(ProcessUtil.getStringS(wppExtList.get(0).getPpboxId()));
                        wppExt.setPalletId(ProcessUtil.getStringS(wppExtList.get(0).getPalletId()));
                        wppExt.setDestCustomerId(ProcessUtil.getStringS(wppExtList.get(0).getDestCustomerId()));

                    }
                    extList.put(panelId, wppExt);
                }
            }
        } catch (Exception e) {
            throw new ETLException2(ErrorCode.ERROR_GET_GP_OPE_HIS_EXT, e);
        }
        return extList;
    }
}
